drop table osoba cascade;
drop table rodzic;
drop table znajomy;
drop table wydarzenie cascade;
drop table wydarzenie_typ cascade;
drop table uczestniczyl_typ cascade;
drop table uczestniczyl;
drop table dokument cascade;
drop table dane_cyfrowe;
drop table gazeta;
drop table akty;
drop table zwiazane;
drop table figuruje;

-- Table: uzytkownik

CREATE LANGUAGE plpgsql;

CREATE TABLE osoba
(
	osoba_id serial PRIMARY KEY,
	imie character varying(15) NOT NULL,
	nazwisko character varying(30) NOT NULL,
	data_ur date,
	adres text
);

-- Table: rodzic

CREATE TABLE rodzic
(
	rodzic integer REFERENCES osoba(osoba_id) ON DELETE CASCADE ON UPDATE CASCADE,
	dziecko integer REFERENCES osoba(osoba_id) ON DELETE CASCADE ON UPDATE CASCADE,
	opis character varying(6) CHECK (opis = 'matka'::character varying(6) or opis = 'ojciec'::character varying(6)),
	CHECK (rodzic != dziecko),
	PRIMARY KEY (dziecko,opis)
);

-- Table: znajomy

CREATE TABLE znajomy
(
	ja integer REFERENCES osoba(osoba_id) ON DELETE CASCADE ON UPDATE CASCADE,
	znajomy integer REFERENCES osoba(osoba_id) ON DELETE CASCADE ON UPDATE CASCADE,
	opis text,
	check (ja != znajomy) 
);

-- Table: wydarzenie_typ , chcemy aby typ wydarzenie byl z podanego zbioru

CREATE TABLE wydarzenie_typ
(
	typ character varying(15) PRIMARY KEY
);

-- dodanie domyslnego wydarzenia
insert into wydarzenie_typ(typ) select 'inne'::character varying(15);


-- Table: wydarzenie

CREATE TABLE wydarzenie
(
	wydarzenie_id serial PRIMARY KEY,
	miejsce text NOT NULL,
	data date NOT NULL,
	opis text,
	typ character varying(15) DEFAULT 'inne'::character varying(15) REFERENCES wydarzenie_typ ON DELETE SET DEFAULT ON UPDATE CASCADE

);

-- Table: uczestniczyl_typ , predefiniowane role dla uczestniczyl

CREATE TABLE uczestniczyl_typ
(
	typ character varying(15) PRIMARY KEY
);

-- dodanie domyslnego wydarzenia
insert into uczestniczyl_typ(typ) select 'inne'::character varying(15);
insert into uczestniczyl_typ(typ) select 'gosc'::character varying(15);

-- Table: uczestniczyl

CREATE TABLE uczestniczyl
(
	osoba_id integer REFERENCES osoba ON DELETE CASCADE ON UPDATE CASCADE,
	wydarzenie_id integer REFERENCES wydarzenie ON DELETE CASCADE ON UPDATE CASCADE,
	typ character varying(15) DEFAULT 'inne'::character varying(15) REFERENCES uczestniczyl_typ ON DELETE SET DEFAULT ON UPDATE CASCADE	
);

-- Table: dokument , tablica IS A

CREATE TABLE dokument
(
	dokument_id serial PRIMARY KEY
);

-- Table: dane_cyfrowe

CREATE TABLE dane_cyfrowe
(
	dokument_id integer PRIMARY KEY REFERENCES dokument ON DELETE CASCADE ON UPDATE CASCADE,
	dane bytea NOT NULL
);

-- Table: gazeta
CREATE TABLE gazeta
(
	dokument_id integer PRIMARY KEY REFERENCES dokument ON DELETE CASCADE ON UPDATE CASCADE,
	nazwa character varying(30) NOT NULL,
	numer integer	
);

-- Table: akty

CREATE TABLE akty
(
	dokument_id integer PRIMARY KEY REFERENCES dokument ON DELETE CASCADE ON UPDATE CASCADE,
	opis text NOT NULL,
	polozenie text,
	ksero bytea
);

-- Table: zwiazane

CREATE TABLE zwiazane
(
	wydarzenie_id integer REFERENCES wydarzenie ON DELETE CASCADE ON UPDATE CASCADE,
	dokument_id integer REFERENCES dokument ON DELETE CASCADE ON UPDATE CASCADE
);

-- Table: figuruje

CREATE TABLE figuruje
(
	osoba_id integer REFERENCES osoba ON DELETE CASCADE ON UPDATE CASCADE,
	dokument_id integer REFERENCES dokument ON DELETE CASCADE ON UPDATE CASCADE
);

--trigery

CREATE FUNCTION wstaw_dane_cyfrowe() returns trigger as
$body$
begin
new.dokument_id := (select nextval('dokument_dokument_id_seq'));
insert into dokument(dokument_id) VALUES(new.dokument_id);
return new;
end
$body$
LANGUAGE plpgsql;

create trigger wstaw_dane_cyfrowe before insert
on dane_cyfrowe for each row
execute procedure wstaw_dane_cyfrowe();

CREATE FUNCTION wstaw_gazeta() returns trigger as
$body$
begin
new.dokument_id := (select nextval('dokument_dokument_id_seq'));
insert into dokument(dokument_id) VALUES(new.dokument_id);
return new;
end
$body$
LANGUAGE plpgsql;

create trigger wstaw_gazeta before insert
on gazeta for each row
execute procedure wstaw_gazeta();

CREATE FUNCTION wstaw_akty() returns trigger as
$body$
begin
new.dokument_id := (select nextval('dokument_dokument_id_seq'));
insert into dokument(dokument_id) VALUES(new.dokument_id);
return new;
end
$body$
LANGUAGE plpgsql;

create trigger wstaw_akty before insert
on akty for each row
execute procedure wstaw_akty();

create function jestPotomkiem(potomek integer,cel integer) returns boolean as
$body$
declare 
r rodzic;
begin
if (potomek = cel)
then
	return true;
end if;
FOR r IN (select * from rodzic Ro where dziecko = potomek) LOOP
	if (jestPotomkiem(r.rodzic,cel))
	then
		return true;
	end if;
END LOOP;
return false;
end
$body$
LANGUAGE plpgsql;

ALTER TABLE figuruje
ADD COLUMN rola character varying(20);



ALTER TABLE osoba
ADD COLUMN login character varying(30) UNIQUE NOT NULL;

ALTER TABLE dokument
ADD COLUMN czas_dodania timestamp DEFAULT now();

ALTER TABLE wydarzenie
ADD COLUMN czas_dodania timestamp DEFAULT now();

ALTER TABLE znajomy
ADD COLUMN czas_dodania timestamp DEFAULT now();


ALTER TABLE osoba
ADD COLUMN typ integer DEFAULT 0;

CREATE OR REPLACE FUNCTION figuruje_bierzace() returns trigger as
$body$
begin
UPDATE figuruje SET rola = 'zdjecie' where rola = 'bierzace' and osoba_id = new.osoba_id;
return new;
end
$body$
LANGUAGE plpgsql;

create trigger wstaw_figuruje_bierzace before insert
on figuruje for each row
execute procedure figuruje_bierzace();




CREATE OR REPLACE FUNCTION dodajdanecyfrowe(uid integer,rd character varying(20), zasob bytea) returns void  as
$body$
declare
did integer;
begin
insert into dane_cyfrowe(dane) values(zasob) returning dokument_id into did;
insert into figuruje(osoba_id,dokument_id,rola) values(uid,did,rd);
end
$body$
LANGUAGE plpgsql;

create table zaproszenie_do_znajomych
(
	od_id integer REFERENCES osoba(osoba_id) ON DELETE CASCADE ON UPDATE CASCADE,
	do_id integer REFERENCES osoba(osoba_id) ON DELETE CASCADE ON UPDATE CASCADE,
	odebrane boolean DEFAULT false NOT NULL,
	czas_dodania timestamp DEFAULT now(),
	UNIQUE(od_id,do_id),
	CHECK (od_id != do_id)
);

create table zaproszenie_do_wydarzenia
(
	od_id integer REFERENCES osoba(osoba_id) ON DELETE CASCADE ON UPDATE CASCADE,
	do_id integer REFERENCES osoba(osoba_id) ON DELETE CASCADE ON UPDATE CASCADE,
	odebrane boolean DEFAULT false NOT NULL,
	czas_dodania timestamp DEFAULT now(),
	rola character varying(15) DEFAULT 'inne'::character varying(15) REFERENCES uczestniczyl_typ ON DELETE SET DEFAULT ON UPDATE CASCADE,
	UNIQUE(od_id,do_id),
	CHECK (od_id != do_id)
);

create table wiadomosc
(
	
	od_id integer REFERENCES osoba(osoba_id) ON DELETE CASCADE ON UPDATE CASCADE,
	do_id integer REFERENCES osoba(osoba_id) ON DELETE CASCADE ON UPDATE CASCADE,
	odebrane boolean DEFAULT false NOT NULL,
	czas_dodania timestamp DEFAULT now(),
	tresc text NOT NULL,
	UNIQUE(od_id,do_id),
	CHECK (od_id != do_id)
);

CREATE OR REPLACE FUNCTION dodaj_zaproszenie_do_znajomych() returns trigger as
$body$
declare
wartosc integer;
begin
if ((select count(*) from znajomy where ja=new.od_id and znajomy=new.do_id LIMIT 1) = 1)
then 
	return null;
end if;
if ((select count(*) from zaproszenie_do_znajomych where od_id = new.od_id and do_id = new.do_id) = 1)
then
	UPDATE zaproszenie_do_znajomych SET odebrane = DEFAULT , czas_dodania = DEFAULT where od_id = new.od_id and do_id = new.do_id;
	return null;
end if;
if ((select count(*) from zaproszenie_do_znajomych where od_id=new.do_id and do_id = new.od_id) = 1)
then
	DELETE FROM zaproszenie_do_znajomych where od_id=new.do_id and do_id = new.od_id;
	INSERT INTO znajomy(ja,znajomy) VALUES(new.od_id,new.do_id);
	INSERT INTO znajomy(ja,znajomy) VALUES(new.do_id,new.od_id);
	return null;
end if;
return new;
end
$body$
LANGUAGE plpgsql;

create trigger dodaj_zaproszenie_do_znajomych before insert
on zaproszenie_do_znajomych for each row
execute procedure dodaj_zaproszenie_do_znajomych();



















